from peewee import SqliteDatabase, Model, PrimaryKeyField, \
    CharField, IntegerField, BooleanField, \
    ForeignKeyField, fn

db = SqliteDatabase("sample.db")


class BaseModel(Model):

    class Meta:
        database = db


class Course(BaseModel):
    id = PrimaryKeyField()
    title = CharField(null=False)
    period = IntegerField()
    description = CharField()

    class Meta:
        order_by = ('title',)
        db_table = 'course'


class Teacher(BaseModel):
    id = PrimaryKeyField()
    name = CharField(null=False)
    gender = BooleanField()
    address = CharField()
    course_id = ForeignKeyField(Course, to_field='id',
                                related_name='course')

    class Meta:
        order_by = ('name',)
        db_table = 'teacher'


if __name__ == '__main__':
    # create table, execute only once
    Course.create_table()
    Teacher.create_table()
    Course.create(id=1, title='经济学',
                  period=320, description='文理科学生均可选修')
    Course.create(id=2, title='大学英语',
                  period=300, description='大一学生必修课')
    Course.create(id=3, title='大学数学',
                  period=310, description='大一学生数学课')
    Teacher.create(name='刘大川', gender=True,
                   address='上海市浦东新区001号', course_id=1)
    Teacher.create(name='淘气', gender=False,
                   address='上海市浦东新区002号', course_id=3)
    Teacher.create(name='刘小川', gender=True,
                   address='上海市浦东新区003号', course_id=2)

    # select
    record = Course.get(Course.title == '经济学')
    print('课程: {}, 学时: {}'.format(record.title, record.period))

    # update
    record.period = 200
    record.save()
    print('已更新! 课程: {}, 学时: {}'.format(record.title, record.period))

    # delete
    # record.delete_instance()

    # select all record
    courses = Course.select()
    for course in courses:
        print(course)

    # select with where clause
    courses = Course.select().where(Course.id < 10).order_by(Course.period.desc())
    for course in courses:
        print(course)

    # sql function
    total = Course.select(fn.Avg(Course.period).alias('avg_period'))
    print("Total period: {}".format(total))

    # update more record
    Course.update(period=300).where(Course.id > 100).execute()

    # join
    record = Course.select().join(Teacher).where(Teacher.gender is True)
    print(record)
